PG 一樣使用 MVCC 無鎖實現 Isolation RC & RR Level，但如果是 Write Skew & Phantom Read 呢？

PG 如何處理 Write Skew 問題？

首先 MySQL 使用 row lock 解決 write skew 問題，如果在 Serializable Isolation Level 會把所有 SELECT 加上讀鎖，而 PG 在 Serializable Isolation Level 是使用 Serializable Snapshot Isolation (SSI) 機制解決 write skew 問題。

SSI 會建立交易依賴圖，若偵測多個 Transaction 對某筆資料形成 read write 環狀 dependency，就只允許 commit 一個 transaction，abort 其他 transaction，例如：

# transaction A
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT id, quantity FROM products WHERE id = ?;

if quantity > 0 
  UPDATE products SET quantity = quantity - 1 WHERE id = ?
COMMIT

# transaction B
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT id, quantity FROM products WHERE id = ?;

if quantity > 0 
  UPDATE products SET quantity = quantity - 1 WHERE id = ?
COMMIT

transaction A & B SELECT products 資料時，PG 會對資料上 SIREAD (Serializable read lock)，該 lock 不卡住任何讀寫，單純標記有哪個 transaction 讀取到他，當 transaction B 去更新 products 時 SSI 會建立 A read 依賴 B write 關連，transaction A 去更新 products 也會建立 B read 依賴 A write 關連，並偵測到有環狀依賴，就會 abort 後面更新的 transaction。

PG 如何處理 Phantom Read 問題？一樣用 Gap Lock 嗎？

PG 沒有 Gap Lock，而是在 UPDATE 時保留 Begin 的 Snapshot，因此 UPDATE 的資料會跟一開始 SELECT 的資料同一批：

BEGIN
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT count(1) FROM tasks WHERE status = '待處理' // => return 10 筆

if count <= 10
   UPDATE tasks SET status = '處理中' WHERE status = '待處理' // => update 10 筆
COMMIT

就算中間有其他 Transaction INSERT ， UPDATE 也只會更新一開始 SELECT 的資料。

聽起來 MySQL 也可實作這個 Snapshot 去解決 Phantom Read，那為何 MySQL 要 Gap Lock？

原因是 MySQL 在 UPDATE or DELETE 時，需要建立新的 Page View (Snapshot)，不然會導致下面的問題：

1. TxA - SELECT balance FROM account WHERE id = 1; => return 100
2. TxB - UPDATE account SET balance = balance+100 WHERE id = 1; 並 COMMIT
3. TxA - UPDATE account SET balance = balance+100 WHERE id = 1;
   • 用 Begin 時的 Snapshot 更新 balance 變成 200
   • 新的 snapshot 會更新成 300

因此 MySQL 要建立新 Snapshot 才能保證更新的值是正確的，UPDATE 時就會更新到別人 INSERT 的資料。

而 PG 用版本檢查的樂觀鎖，當 TxA UPDATE account 時，發現 tuple 的 xmax 有異動代表被其他 transaction 更新過了，就 abort 這個 UPDATE ，因此不用 Gap Lock 就可解決 Phantom Read。

PG 的 Lock 很單純，只有 Table Lock 跟 Row Lock ，Lock 不只分讀寫鎖，還有不同模式：

• FOR UPDATE：完全寫鎖。
• FOR NO KEY UPDATE：只對 key (primary key + unique index) 上寫鎖，非 key column 可更新
• FOR SHARE：完全讀鎖。
• FOR KEY SHARE：只對 key 上讀鎖。
  此外 PG 是單引擎架構，上鎖跟 filter 的 process 是同一個，因此即便 SELECT * FROM uesrs WHERE name = 'vic' FOR UPDATE name 沒有 index 變 full table scan，也不會 table lock，可 filter 完在上鎖。

整體而言，PG 在 serializable level 併發效能更好，也沒有 gap lock 降低 deadlock 風險，鎖精準度更高，使用上更單純，還有不同模式能適應不同場情提升併發效能。

此外 PG 在記憶體結構的設計也比 MySQL 更適合併發。

PG 記憶體是用什麼結構？為何併發效能更好？

PG 會把讀寫資料也暫存到記憶體 Shared Buffer 中，使用結構為 Clock Sweep 的變體 LRU。

Clock Sweep 會紀錄每個 Page 的 usage_count ，每讀取一次就加一，另外有一個 Sweeper 定期從 Sweep Pointer 掃描 Page 將 usage_count 減一，當 usage_count < 0 就移除該 Page。

Clock Sweep 看似像 LFU，但 LFU 不會定期減少 usage_count，無法適應熱點變化的情況，Clock Sweep 能保存長期被讀取資料跟適應熱點變化。

Clock Sweep 優點是查詢後不用搬節點，只更新 usage_count，不用對整個結構上鎖，併發讀效能好，但清快取要等 Sweeper 掃描，且每次只 Scan N 筆 Page，可能有 Page 可移除但沒被 Scan，當熱點變化時，沒法即時移除空間，可能導致新讀取資料放不進快取降低命中率。

PG 如何克服 Full Table Scan 載入大量資料到記憶體排除掉熱點資料？

PG 的 query 執行器會分析語法，當發現要執行順序 scan 時 (e.g SELECT * FROM big_table) 會啟用 Buffer Access Strategy，在快取中建立固定大小的 circular list，循環使用該空間儲存 full table scan 的 Page，因此只影響小部分快取資料。

PG 如何將資料同步到硬碟中的 Heap？

PG 一樣有 WAL 實現 Durability，並透過 background checkpoint process 將 dirty page 更新到 Heap 中。

不像 MySQL 使用 FIFO Flush List 儲存 Dirty Page 確保更新順序，要維護額外的結構跟鎖，但可調節每次 I/O 的量。

PG checkpoint process 會使用 Write LSN & Redo LSN：

• Write LSN：WAL 最後寫入 LSN
• Redo LSN：最小寫進 Heap 的 LSN
  checkpoint 執行時，會 snapshot 當下 Write LSN ，用 Write LSN snapshot 值更新 Redo LSN ，隨後把所有 dirty page <= Redo LSN (snapshot 的 Write LSN) 都寫進 Heap，全部寫進後才啟用更新後的 Redo LSN。

該方法不用額外的結構跟鎖，但不像 MySQL 可精準調節 I/O 量，而是透過：

• checkpoint_timeout：checkpoint process 執行的間隔時間，例如 5min 是每 5min 執行一次
• max_wal_size：checkpoint process 最多 wal 累積量，例如 1G 是 wal 寫入 1G 後要立即執行
• checkpoint_completion_target：為了避免 checkpoint 執行時，一次要 flush 大量 dirty page，該參數用於設定 checkpoint process 總執行時間，例如 0.9 代表總共可以執行間隔時間 * 0.9 的時間慢慢 I/O。

當 checkpoint_timeout=10min & checkpoint_completion_target=0.8 時，checkpoint process 最多可以執行 10分鐘 × 0.8 = 8分鐘，如果 dirty pages 有 1000，那麼平均 I/O 為 125 page/min，就不會一次把 1000 page 寫進 Heap。